package com.wuye.repository;

import org.springframework.data.jpa.repository.*;
import java.util.*;
import com.wuye.model.*;

public interface OrderRepository extends JpaRepository<Order,String>{
	//根据当前日期查询订单详情
	@Query(nativeQuery = true,value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') order by order_date desc")
	public List<Order> findOrderByOrderDate_Desc();
	//根据用户输入的信息查询订单详情
	@Query(nativeQuery = true,value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') = ?1 order by order_date desc")
	public List<Order> findOrderByOrderDate(String times);
	//根据用户输入的日期信息和订单状态查询订单详情
	@Query(nativeQuery = true,value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') = ?1 and order_state = ?2 order by order_date desc")
	public List<Order> findAllOrders(String times,String orderStatus);

	public List<Order> findAllByOrderByOrderDate_Desc();

	@Query(nativeQuery = true,value = "select * from wuye_order where order_state = 'CONFIRM' and  ?1 < order_date and order_date < ?2")
	public List<Order> findAllByOrderByOrderDate_Desc(String start,String end);

	@Query(nativeQuery = true,value = "select * from wuye_order where if(?3='' ,1=1 ,order_state = ?3)  and  ?1 < order_date and order_date < ?2 order by order_date desc")
	public List<Order> findByOrderDateAndState(String start,String end,String state);

	public Order findByOrderNum(String orderNum);
	//根据订单状态查询订单详情
	public List<Order> findByUserIdOrderByOrderDate_Desc(String userId);
	@Query(nativeQuery=true,value = "select * from wuye_order where order_state = ?1 and DATE_FORMAT(order_date,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') order by order_date desc")
	public List<Order> findsByOrderStatus(String orderStatus);



	//查询截止日期内的所有订单
	@Query(nativeQuery = true, value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') <= ?1 order by order_date desc")
	public List<Order> findAllByEndtime(String endTime);
	//根据订单状态查询截止日期内的订单
	@Query(nativeQuery = true, value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') <= ?1 and order_state = ?2 order by order_date desc")
	public List<Order> findAllByEndtimeAndStatus(String endTime,String status);

	//查询起始时间到截止时间内的所有订单
	@Query(nativeQuery = true,value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') >= ?1 and DATE_FORMAT(order_date,'%Y-%m-%d') <= ?2 order by order_date desc")
	public List<Order> findAllByStarttimeAndEndtime(String beginTime,String endTime);

	//根据订单状态查询起始时间到截止时间内的订单
	@Query(nativeQuery = true,value="select * from wuye_order where DATE_FORMAT(order_date,'%Y-%m-%d') >= ?1 and DATE_FORMAT(order_date,'%Y-%m-%d') <= ?2 and order_state = ?3 order by order_date desc")
	public List<Order> findAllByStarttimeAndEndtimeAndState(String beginTime,String endTime,String status);


	//查询所有订单
	@Query(nativeQuery = true, value="select * from wuye_order order by order_date desc")
	public List<Order> findsAllOrders();


	@Query(nativeQuery = true,value = "select * from wuye_order where order_state = 'COMPLETE' and  ?1 < order_date and order_date < ?2")
	public List<Order> findOrdAllByOrderByOrderDate_Desc(String start,String end);

}